/**
 * @author:albert
 */

package com.bkbw.live.database.util;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.bkbw.live.common.database.DbDesc;
import com.bkbw.live.common.util.Utils;
import com.bkbw.live.database.common.DatabaseConfig;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class DatabaseUtil {
	private static Logger log = LoggerFactory.getLogger(DatabaseConfig.LoggerName);
	
	public static String transformColumnName(String []tmps){
		String key = "";
		for (int j = 0; j < tmps.length; j++) {
            if (j == 0) {
                //key += tmps[j].toLowerCase();// 转换为小写
            	key += tmps[j].substring(0, 1).toLowerCase() + tmps[j].substring(1);//首字母小写
            } else {
                key += tmps[j].substring(0, 1).toUpperCase()
                        + tmps[j].substring(1).toLowerCase();
            }
        }
		return key;
	}
	
	public static List<Map<String, Object>> resultSetToMapList(ResultSet rs) {
		ArrayList<Map<String, Object>> result = new ArrayList<Map<String,Object>>();
		try {
			int iColumn = rs.getMetaData().getColumnCount();
			while (rs.next()) {
				Map<String, Object> map = new LinkedHashMap<String, Object>();
				for (int i = 1; i <= iColumn; i++) {
	                String tmpkey = rs.getMetaData().getColumnName(i);// 通过索引找到行的名字

	                String[] tmps = tmpkey.split("_");// 分割所有含"_"字符，返回数组
	                String key = transformColumnName(tmps);

	                Object val = null;
	                // switch行类型
	                switch (rs.getMetaData().getColumnType(i)) {
	                case Types.DATE:
	                    val = rs.getDate(i);
	                    break;
	                case Types.DOUBLE:
	                    if(rs.getObject(i)==null)
	                        val=null;
	                    else
	                        val = rs.getDouble(i);
	                    break;
	                case Types.NUMERIC:
	                    if(rs.getObject(i)==null)
	                        val=null;
	                    else
	                        val = rs.getDouble(i);
	                    break;
	                case Types.DECIMAL:
	                    if(rs.getObject(i)==null)
	                        val=null;
	                    else
	                        val = rs.getDouble(i);
	                    break;
	                case Types.INTEGER:
	                    if(rs.getObject(i)==null)
	                        val=null;
	                    else
	                        val = rs.getInt(i);
	                    break;
	                default:
	                    val = rs.getString(i);
	                    break;
	                }

	                if (null != val) {
	                    // map找找不到指定键便设置个
	                    if (!map.containsKey(key)) {
	                        map.put(key, val);
	                    }
	                }
	            }
	            result.add(map);
			}
		}
		catch (Exception e){
			log.error(e.getMessage());
		}
		finally {
			try {
	            if (rs != null)
	                rs.close();
	            rs = null;
	        } catch (SQLException e) {
	            log.error(e.getMessage());
	        }
			
		}
		return result;
	}
	
	// P for protobuf message, B for protobuf message.builder
	public static<PROTO, BUILDER> List<PROTO> resultSetToProtoBean(ResultSet rs, Class<PROTO> clazz, Class<BUILDER> builderClazz) {
		try {
			List<PROTO> list = new ArrayList<PROTO>();
			ResultSetMetaData metaData = rs.getMetaData();
			int iColumn = rs.getMetaData().getColumnCount();
			while (rs.next()) {
				Method newBuilder = clazz.getMethod("newBuilder");
				Object t = newBuilder.invoke(null);
				BUILDER bean = (BUILDER)t;
				for (int i = 0; i < iColumn; ++i){
					
					String fieldName = metaData.getColumnName(i + 1);
					//log.debug(fieldName);
					
					Method getter = null;
					// db中有这个字段，但是bean中没有，跳过它
					try {
						getter = builderClazz.getMethod("get"+fieldName);
					}
					catch (NoSuchMethodException e){
						//e.printStackTrace();
						log.debug("exception class " + clazz.getName() + " field " +fieldName);
						continue;
					}
					
					Method setter = null;
					try {
						setter = builderClazz.getMethod("set"+fieldName, getter.getReturnType());
					}
					catch (NoSuchMethodException e){
						//e.printStackTrace();
						log.debug("exception class " + clazz.getName() + " field " +fieldName);
						continue;
					}
					
					if (getter == null || setter == null) {
						continue;
					}
					
					//String value = rs.getString(i + 1);
					Object value = rs.getObject(i + 1);
					if (value == null) {
						continue;
					}
					//log.debug(value.getClass().toString());
					//log.debug(getter.getReturnType().toString());
					//log.debug(""+rs.getMetaData().getColumnType(i + 1));
					try {
						setter.invoke(bean, value);
					}
					catch (Exception e){
						e.printStackTrace();
						log.debug("field getter "+getter.toString()+" setter "+setter.toString());
					}
				}
				Method build = builderClazz.getMethod("build");
				PROTO protoBean = (PROTO)build.invoke(bean);
				list.add(protoBean);
			}
			return list;
		}
		catch (Exception e){
			e.printStackTrace();
			log.debug(Utils.exceptionStack(e));
		}
		return null;
	}
	
	public static<T> List<T> resultSetToBean(ResultSet rs, Class<T> clazz) {
		
		try {
			List<T> list = new ArrayList<T>();
			ResultSetMetaData metaData = rs.getMetaData();
			int iColumn = rs.getMetaData().getColumnCount();
			while (rs.next()) {
				T bean = clazz.newInstance();
				for (int i = 0; i < iColumn; ++i){
					String fieldName = metaData.getColumnName(i + 1);
					Method getter = null;
					// db中有这个字段，但是bean中没有，跳过它
					try {
						getter = clazz.getMethod("get"+fieldName);
					}
					catch (NoSuchMethodException e){
						//e.printStackTrace();
						log.debug("exception class " + clazz.getName() + " field " +fieldName);
						continue;
					}
					
					Method setter = null;
					try {
						setter = clazz.getMethod("set"+fieldName, getter.getReturnType());
					}
					catch (Exception e){
						//e.printStackTrace();
						log.debug("exception class " + clazz.getName() + " field " +fieldName);
						continue;
					}
					
					if (getter == null || setter == null) {
						continue;
					}
					
					//String value = rs.getString(i + 1);
					Object value = rs.getObject(i + 1);
					try {
						setter.invoke(bean, value);
					}
					catch (Exception e){
						e.printStackTrace();
						log.debug("field getter "+getter.toString()+" setter "+setter.toString());
					}
				}
				list.add(bean);
			}
			return list;
		}
		catch (Exception e){
			e.printStackTrace();
			log.debug(Utils.exceptionStack(e));
		}
		return null;
	}
	
	public static JSONArray resultSetToJson(ResultSet rs) {
		JSONArray ret = new JSONArray();
		try {
			int iColumn = rs.getMetaData().getColumnCount();
			while (rs.next()) {
				Map<String, Object> map = new LinkedHashMap<String, Object>();
				for (int i = 1; i <= iColumn; i++) {
	                String tmpkey = rs.getMetaData().getColumnName(i);// 通过索引找到行的名字

	                String[] tmps = tmpkey.split("_");// 分割所有含"_"字符，返回数组
	                String key = transformColumnName(tmps);

	                Object val = null;
	                // switch行类型
	                switch (rs.getMetaData().getColumnType(i)) {
	                case Types.DATE:
	                    val = rs.getDate(i);
	                    break;
	                case Types.DOUBLE:
	                    if(rs.getObject(i)==null)
	                        val=null;
	                    else
	                        val = rs.getDouble(i);
	                    break;
	                case Types.NUMERIC:
	                    if(rs.getObject(i)==null)
	                        val=null;
	                    else
	                        val = rs.getDouble(i);
	                    break;
	                case Types.DECIMAL:
	                    if(rs.getObject(i)==null)
	                        val=null;
	                    else
	                        val = rs.getDouble(i);
	                    break;
	                case Types.INTEGER:
	                    if(rs.getObject(i)==null)
	                        val=null;
	                    else
	                        val = rs.getInt(i);
	                    break;
	                case Types.BIGINT:
	                	if(rs.getObject(i)==null)
	                        val=null;
	                    else
	                        val = rs.getLong(i);
	                    break;
	                default:
	                    val = rs.getString(i);
	                    break;
	                }

	                if (null != val) {
	                    // map找找不到指定键便设置个
	                    if (!map.containsKey(key)) {
	                        map.put(key, val);
	                    }
	                }
	            }
				JSONObject jsonObject = JSONObject.fromObject(map);
	            ret.add(jsonObject);
			}
		}
		catch (Exception e){
			e.printStackTrace();
			log.error(e.getMessage());
		}
		finally {
			try {
	            if (rs != null)
	                rs.close();
	            rs = null;
	        } catch (SQLException e) {
	            log.error(e.getMessage());
	        }
			
		}
		return ret;
	}
	
	public static JSONArray executeQuery(DataSource ds, String sql) throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = ds.getConnection();
			stmt = conn.prepareStatement(sql);
			ResultSet resultSet = stmt.executeQuery();
			JSONArray jsonArray = resultSetToJson(resultSet);
			return jsonArray;
		}
		catch (Exception e){
			e.printStackTrace();
			return null;
		}
		finally {
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
			if (conn != null) {
				conn.close();
				conn = null;
			}
		}
	}
	
	public static int getTableRowCount(DataSource ds, String table) throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = ds.getConnection();
			stmt = conn.prepareStatement("select count(*) from "+table);
			ResultSet resultSet = stmt.executeQuery();
			//JSONArray jsonArray = resultSetToJson(resultSet);
			resultSet.next();
			return resultSet.getInt(1);
		}
		catch (Exception e){
			e.printStackTrace();
			return 0;
		}
		finally {
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
			if (conn != null) {
				conn.close();
				conn = null;
			}
		}
	}
	
	public static<T> List<T> executeQueryReturnBean(DataSource ds, String sql, Class<T> clazz) throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = ds.getConnection();
			stmt = conn.prepareStatement(sql);
			ResultSet resultSet = stmt.executeQuery();
			return resultSetToBean(resultSet, clazz);
		}
		catch (Exception e){
			e.printStackTrace();
			return null;
		}
		finally {
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
			if (conn != null) {
				conn.close();
				conn = null;
			}
		}
	}
	
	//public static<P, B> List<P> resultSetToProtoBean(ResultSet rs, Class<P> clazz, Class<B> builderClazz) {
	public static<PROTO, BUILDER> List<PROTO> executeQueryReturnProtoBean(DataSource ds, String sql, Class<PROTO> clazz, Class<BUILDER> builderClazz) throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = ds.getConnection();
			stmt = conn.prepareStatement(sql);
			ResultSet resultSet = stmt.executeQuery();
			return resultSetToProtoBean(resultSet, clazz, builderClazz);
		}
		catch (Exception e){
			e.printStackTrace();
			return null;
		}
		finally {
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
			if (conn != null) {
				conn.close();
				conn = null;
			}
		}
	}
	
	public static JSONArray executeQueryInTrans(Connection conn, String sql) throws Exception{
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(sql);
			ResultSet resultSet = stmt.executeQuery();
			JSONArray jsonArray = resultSetToJson(resultSet);
			return jsonArray;
		}
//		catch (Exception e){
//			e.printStackTrace();
//			return null;
//		}
		finally {
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
		}
	}
	
	public static int getTableRowCount(Connection conn, String table) throws Exception{
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement("select count(1) from "+table);
			ResultSet resultSet = stmt.executeQuery();
			resultSet.next();
			return resultSet.getInt(1);
		}
		catch (Exception e){
			e.printStackTrace();
			return 0;
		}
		finally {
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
		}
	}
	
	
	public static<T> List<T> executeQueryInTransReturnBean(Connection conn, String sql, Class<T> clazz) throws Exception {
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(sql);
			ResultSet resultSet = stmt.executeQuery();
			return resultSetToBean(resultSet, clazz);
			//return resultSet;
		}
//		catch (Exception e){
//			e.printStackTrace();
//			return null;
//		}
		finally {
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
		}
	}
	
	public static<PROTO, BUILDER> List<PROTO> executeQueryInTransReturnProtoBean(Connection conn, String sql, Class<PROTO> clazz, Class<BUILDER> builderClazz) throws Exception{
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(sql);
			ResultSet resultSet = stmt.executeQuery();
			return resultSetToProtoBean(resultSet, clazz, builderClazz);
			//return resultSet;
		}
//		catch (Exception e){
//			e.printStackTrace();
//			return null;
//		}
		finally {
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
		}
	}
	
	//public static JSONArray executeProc
	
	public static int executeUpdate(DataSource ds, String sql) throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = ds.getConnection();
			stmt = conn.prepareStatement(sql);
            return stmt.executeUpdate();
		}
		catch (Exception e){
			e.printStackTrace();
			return 0;
		}
		finally {
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
			if (conn != null) {
				conn.close();
				conn = null;
			}
		}
	}
	
	public static int executeUpdateInTrans(Connection conn, String sql) throws Exception{
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(sql);
            return stmt.executeUpdate();
		}
//		catch (Exception e){
//			e.printStackTrace();
//			return 0;
//		}
		finally {
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
		}
	}
	
	public static int executeInsertReturnIntId(DataSource ds, String sql) throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = ds.getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
			int id = 0;
			ResultSet updateRS = stmt.getGeneratedKeys(); 
            if (updateRS.next()) { 
            	id = updateRS.getInt(1);
            }
            return id;
		}
		catch (Exception e){
			e.printStackTrace();
			return 0;
		}
		finally {
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
			if (conn != null) {
				conn.close();
				conn = null;
			}
		}
	}
	
	public static int executeInsertReturnIntIdInTrans(Connection conn, String sql) throws Exception{
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(sql);
			stmt.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
			int id = 0;
			ResultSet updateRS = stmt.getGeneratedKeys(); 
            if (updateRS.next()) { 
            	id = updateRS.getInt(1);
            }
            return id;
		}
//		catch (Exception e){
//			e.printStackTrace();
//			return 0;
//		}
		finally {
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
		}
	}
	
	public static long executeInsertReturnLongId(DataSource ds, String sql) throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = ds.getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
			long id = 0;
			ResultSet updateRS = stmt.getGeneratedKeys(); 
            if (updateRS.next()) { 
            	id = updateRS.getLong(1);
            }
            return id;
		}
		catch (Exception e){
			e.printStackTrace();
			return 0;
		}
		finally {
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
			if (conn != null) {
				conn.close();
				conn = null;
			}
		}
	}
	
	public static long executeInsertReturnLongIdInTrans(Connection conn, String sql) throws Exception{
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(sql);
			stmt.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
			long id = 0;
			ResultSet updateRS = stmt.getGeneratedKeys(); 
            if (updateRS.next()) { 
            	id = updateRS.getLong(1);
            }
            return id;
		}
//		catch (Exception e){
//			e.printStackTrace();
//			return 0;
//		}
		finally {
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
		}
	}
	
	
	public static<T> String beanToUpdateByWhere(T bean, String where) {
		
		try {
			StringBuilder builder = new StringBuilder();
			Class cls = bean.getClass();
			
			Method getTableName = cls.getMethod("getTableName");
			Object tableName = getTableName.invoke(null);
			
//			Method getPrimaryKey = cls.getMethod("getPrimaryKey");
//			Object primaryKey = getPrimaryKey.invoke(null);
			
			builder.append("update " + tableName + " set ");
			
			Field[] fields = cls.getDeclaredFields();
			
			for (Field field : fields){
				field.setAccessible(true);
				String name = field.getName();
				Object value = field.get(bean);
				DbDesc desc = field.getAnnotation(DbDesc.class);
				if (desc != null) {
					if (desc.primarykey()) {
						continue;
					}
					if (desc.autoincrement()) {
						continue;
					}
				}
				if (value == null) {
					continue;
				}
				// skip primary key
//				if (value.equals(primaryKey)) {
//					continue;
//				}
//				if (name.equals(primaryKey)) {
//					continue;
//				}
				if (value.equals(tableName)) {
					continue;
				}
				builder.append(name + "='" + value + "',");
			}
			builder.deleteCharAt(builder.length() - 1);
			builder.append(" where " + where);
			return builder.toString();
		}
		catch (Exception e){
			e.printStackTrace();
			return null;
		}
	}
	
	public static<T> String beanToUpdateByPk(T bean, Object pkValue) {
		try {
			String primaryKey = null;
			Class cls = bean.getClass();
			Field[] fields = cls.getDeclaredFields();
			for (Field field : fields){
				field.setAccessible(true);
				String name = field.getName();
				Object value = field.get(bean);
				DbDesc desc = field.getAnnotation(DbDesc.class);
				if (desc != null) {
					if (desc.primarykey()) {
						primaryKey = name;
						break;
					}
				}
			}
			
			String where = primaryKey + "='" + pkValue + "'";
			return beanToUpdateByWhere(bean, where);
		}
		catch (Exception e){
			return null;
		}
	}
	
	public static<T> String beanToUpdate(T bean) {
		try {
			String primaryKey = null;
			Object pkValue = null;
			Class cls = bean.getClass();
			Field[] fields = cls.getDeclaredFields();
			for (Field field : fields){
				field.setAccessible(true);
				String name = field.getName();
				Object value = field.get(bean);
				DbDesc desc = field.getAnnotation(DbDesc.class);
				if (desc != null) {
					if (desc.primarykey()) {
						primaryKey = name;
						pkValue = value;
						break;
					}
				}
			}
			
			String where = primaryKey + "='" + pkValue + "'";
			return beanToUpdateByWhere(bean, where);
		}
		catch (Exception e){
			return null;
		}
	}
	
	public static<T> String beanToInsert(T bean) {
		
		try {
			StringBuilder builder = new StringBuilder();
			Class cls = bean.getClass();
			
			Method getTableName = cls.getMethod("getTableName");
			Object tableName = getTableName.invoke(null);
			
//			Method getPrimaryKey = cls.getMethod("getPrimaryKey");
//			Object primaryKey = getPrimaryKey.invoke(null);
			
			builder.append("insert " + tableName);
			
			StringBuilder columnsBuilder = new StringBuilder();
			StringBuilder valuesBuilder = new StringBuilder();
			columnsBuilder.append("(");
			valuesBuilder.append("values(");
			Field[] fields = cls.getDeclaredFields();
			
			for (Field field : fields){
				field.setAccessible(true);
				String name = field.getName();
				Object value = field.get(bean);
				DbDesc desc = field.getAnnotation(DbDesc.class);
				if (desc != null) {
					if (desc.autoincrement()) {
						continue;
					}
				}
				if (value == null) {
					continue;
				}
				// skip primary key
//				if (value.equals(primaryKey)) {
//					continue;
//				}
//				if (name.equals(primaryKey)) {
//					continue;
//				}
				if (value.equals(tableName)) {
					continue;
				}
				columnsBuilder.append(name+",");
				valuesBuilder.append("'" + value + "',");
			}
			columnsBuilder.deleteCharAt(columnsBuilder.length() - 1);
			valuesBuilder.deleteCharAt(valuesBuilder.length() - 1);
			columnsBuilder.append(")");
			valuesBuilder.append(")");
			builder.append(" "+columnsBuilder.toString() + " " + valuesBuilder.toString());
			return builder.toString();
		}
		catch (Exception e){
			e.printStackTrace();
			return null;
		}
	}
	
	public static<T> String beanToDelete(T bean) {
		try {
			StringBuilder builder = new StringBuilder();
			Class cls = bean.getClass();
			
			Method getTableName = cls.getMethod("getTableName");
			Object tableName = getTableName.invoke(null);
			
//			Method getPrimaryKey = cls.getMethod("getPrimaryKey");
//			Object primaryKey = getPrimaryKey.invoke(null);
			
			builder.append("delete from " + tableName);
			
			Field[] fields = cls.getDeclaredFields();
			String primaryKey = null;
			Object primaryKeyValue = null;
			
			for (Field field : fields){
				field.setAccessible(true);
				String name = field.getName();
				Object value = field.get(bean);
				DbDesc desc = field.getAnnotation(DbDesc.class);
				
				if (desc != null) {
					if (desc.primarykey()) {
						primaryKey = name;
						primaryKeyValue = value;
						break;
					}
				}
				
			}
			if (primaryKey == null) {
				return null;
			}
			builder.append(" where " + primaryKey + "='" + primaryKeyValue + "'");
			return builder.toString();
		}
		catch (Exception e){
			e.printStackTrace();
			return null;
		}
	}
}
